<?php

class Db{
	protected $db;
	protected $sql;
	protected $select;
	protected $where;
	protected $limit;
	protected $table;
	protected $field = '*';
	protected $order;
	protected $error;
	
	public function __construct(){
		$db = new PDO('mysql:host=localhost;dbname=mvc;charset=utf8', 'root', '');
		$db->setAttribute(PDO::ATTR_CASE, PDO::CASE_LOWER);
		$db->exec("set names 'utf8';");
		$this->db = $db;
	}

	public function execute($query='',$params=array()){
		$res = $this->db->prepare($query);
		return $res->execute($params);
	}

	public function query($query='',$params=array()){
		$res = $this->db->prepare($query);
		$res->execute($params);
		return $res->fetchAll(PDO::FETCH_ASSOC);
	}

	public function exec($query=''){
		return $this->db->exec($query);
	}

	public function select(){
		if(!$this->_checkerror()){
			return false;
		}
		$this->sql = 'SELECT '.$this->field.' FROM '.$this->table.' '.$this->where.' '.$this->order.' '.$this->limit;
		$res = $this->db->query($this->sql);
		if(!$res) return false;
		$res->setFetchMode(PDO::FETCH_ASSOC);	// 关联数组结果
		$data = $res->fetchAll();
		return $data;
	}

	public function find(){
		$this->limit(1);
		return $this->select()[0];
	}

	public function delete(){
		if(!$this->_checkerror()){
			return false;
		}
		$this->sql = 'DELETE FROM '.$this->table.' '.$this->where;
		$res = $this->db->exec($this->sql);
		return $res;
	}

	public function update(){
		if(!$this->_checkerror()){
			return false;
		}
		$this->sql = 'UPDATE '.$this->table.' SET '.$this->df.' = '.$this->dv.' '.$this->where;
		return $this->db->exec($this->sql);
	}

	public function add(){
		if(!$this->_checkerror()){
			return false;
		}
		$this->sql = 'INSERT INTO '.$this->table.' ('.$this->df.') VALUES ('.$this->dv.')';
		$this->db->exec($this->sql);
		return intval($this->db->lastInsertId());
	}

	public function data($data=array()){
		if(!is_array($data)){
			$this->error = '数据必须是数组形式';
			return $this;
		}
		foreach($data as $k=>$v){
			$dataField[] = "`$k`";
			if(is_string($v)){
				$dataValue[] = $this->db->quote($v);
			}else{
				$dataValue[] = $v;
			}
		}
		$this->df = implode(',',$dataField);
		$this->dv = implode(',',$dataValue);
		return $this;
	}

	public function field($field){
		$this->field = $field;
		return $this;
	}

	public function where($arr = array(),$logic='AND'){
		if(!is_array($arr)){
			$this->error = 'where 条件必须为数组形式';
			return $this;
		}
		$this->where = 'WHERE ';
		foreach($arr as $k=>$v){
			if(is_string($v)){
				$v = $this->db->quote($v);
				$this->where .= "`$k` = $v ".$logic.' ';
			}else if(is_array($v)){
				if(!is_string($v[0])){
					$this->error = 'WHERE 条件有错误';
					return $this;
				}
				if(!in_array(trim(strtoupper($v[0])),array('>','>=','<','<=','<>','BETWEEN','IN','LIKE'))){
					$this->error = 'WHERE 查询条件不成立';
					return $this;
				}
				if(in_array(trim(strtoupper($v[0])),array('>','>=','<','<='))){
					if(!is_numeric($v[1])){
						$this->error = '比较查询只适用于数字';
						return $this;
					}
				}
				if(is_string($v[1])){
					$v[1] = $this->db->quote($v[1]);
				}elseif(is_array($v[1])){
					$v[0] = strtoupper($v[0]);
					if($v[0]=='IN'){
						$v[1] = '('.implode(',',$v[1]).')';
					}elseif($v[0]=='BETWEEN'){
						if(count($v[1])!==2){
							$this->error = 'BETWEEN 条件不成立';
							return $this;
						}
						$v[1] = $v[1][0].' AND '.$v[1][1];
					}else{
						$this->error = 'WHERE 条件不成立';
						return $this;
					}
				}
				$this->where .= "`$k` $v[0] $v[1] ".$logic.' ';
			}else{
				$this->where .= "`$k` = $v ".$logic.' ';
			}
		}
		$this->where = substr($this->where, 0, -4);
		return $this;
	}

	public function limit($limit=1){
		$limit = intval($limit);
		$this->limit = 'limit '.$limit;
		return $this;
	}

	public function order($order=''){
		if($order){
			$this->order = 'ORDER BY '.$order;
		}
		return $this;
	}

	public function table($table=''){
		$this->table = $table;
		return $this;
	}

	public function getLastSql(){
		return $this->sql;
	}

	private function _checkerror(){
		return $this->error ? 0 : 1;
	}

	public function getError(){
		if(!$this->error) $this->error = 'SQL语法正确，请确认数据字段及条件与数据的对应关系是否正确';
		return $this->error."\n<br>".$this->sql;
	}



}